package com.ht.pms.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.ht.pms.core.dao.SalaryUploadFileDao;
import com.ht.pms.core.dto.upload.UpLoadTemp;
import com.ht.pms.core.dto.upload.UploadFile;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class SalaryUploadFileDaoImpl implements SalaryUploadFileDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public Integer insertSalaryFile(UploadFile uploadFile) {
		final String sql = "insert into t_upload_file (batch_code, `file_name`, `state`, template_code, time, file_code, import_user_id, import_user_name) values(?, ?, ?, ?, ?, ?, ?, ?)";

		KeyHolder keyHolder = new GeneratedKeyHolder();

		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, uploadFile.getBatchCode());
				ps.setString(2, uploadFile.getFileName());
				ps.setInt(3, uploadFile.getState());
				ps.setString(4, uploadFile.getTemplateCode());
				ps.setString(5, uploadFile.getTime());
				ps.setString(6, uploadFile.getFileCode());
				ps.setString(7, uploadFile.getImportUserId()==null?"":uploadFile.getImportUserId());
				ps.setString(8, uploadFile.getImportUserName()==null?"":uploadFile.getImportUserName());
				return ps;
			}
		}, keyHolder);

		return keyHolder.getKey().intValue();
	}

	@Override
	public Integer insertSalaryFileTemp(UpLoadTemp temp) {
		String sql = "insert into  t_upload_temp(file_name, template_code, file_type, del, batch_code, time, importUserId, importUserName)values(?, ?, ?, ?, ?, ?, ?, ?)";

		return jdbcTemplate.update(sql, temp.getFileName(), temp.getTemplateCode(), temp.getFileType(), temp.getDel(),
				temp.getBatchCode(), temp.getTime(), temp.getImportUserId(), temp.getImportUserName());
	}

	@Override
	public UpLoadTemp selectUploadTempByBatchCode(String batchCode) {
		String sql = "select * from t_upload_temp where batch_code = ?";
		try {
			return jdbcTemplate.queryForObject(sql, new Object[] { batchCode },
					new BeanPropertyRowMapper<UpLoadTemp>(UpLoadTemp.class));
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}
	
	@Override
	public UpLoadTemp selectUploadTempByTemplateCodeAndTime(String relationTemplateCode, String time) {
		String sql = "select * from t_upload_temp where batch_code = ? and time = ?";
		try {
			return jdbcTemplate.queryForObject(sql, new Object[] { relationTemplateCode, time },
					new BeanPropertyRowMapper<UpLoadTemp>(UpLoadTemp.class));
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	/**
	 * 修改小批次记录
	 */
	@Override
	public Integer updateSalaryFileRecord(int successNo, int failNo, Integer fileId) {
		String sql = "update t_upload_file set succes_number = ? , faile_number = ? where file_id = ?";

		return jdbcTemplate.update(sql, successNo, failNo, fileId);
	}

	@Override
	public Integer selectSalaryFileRecord(Integer fileId , String tableName, Integer state) {
		String sql = "select count(*) from "+tableName+" where in_state = ? and file_id = ?";
		
		return jdbcTemplate.queryForObject(sql, Integer.class, state, fileId);
	}

	@Override
	public UploadFile selectUploadFileByFileId(Integer fileId) {
		String sql = "select * from t_upload_file where file_id = ?";
		try {
			return jdbcTemplate.queryForObject(sql, new Object[] { fileId },
					new BeanPropertyRowMapper<UploadFile>(UploadFile.class));
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@Override
	public List<UploadFile> selectUploadFileByBatchCode(String batchCode) {
		String sql = "SELECT * FROM t_upload_file where batch_code = ?";
		return jdbcTemplate.query(sql,new Object[] {batchCode}, new BeanPropertyRowMapper<UploadFile>(UploadFile.class));
	}

	@Override
	public int updateSalaryFileAuth(Integer salaryNo, Integer fileId) {
		String sql = "update t_upload_file set state = ? where file_id = ?";

		return jdbcTemplate.update(sql, salaryNo, fileId);
	}

	@Override
	public UploadFile selectUploadFileByTime(String time, String templateCode) {
		String sql = "select * from t_upload_file where time = ? and template_code = ? ORDER BY create_time desc ";
		
		List<UploadFile> list = jdbcTemplate.query(sql,new Object[] {time, templateCode}, new BeanPropertyRowMapper<UploadFile>(UploadFile.class));
		if(list == null || list.isEmpty()) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public int updateSalaryFileState(UploadFile uploadFile2) {
		String sql = "update t_upload_file set is_del = ? where batch_code = ?";

		return jdbcTemplate.update(sql, uploadFile2.getIsDel(), uploadFile2.getBatchCode());
	}

	@Transactional(rollbackFor = Exception.class)
	@Override
	public void confirmData(String fileId, String jobCode,String tableName) {

		String deleteUploadLogSql = "DELETE FROM t_upload_file_log WHERE file_id='"+fileId+"' AND job_code='"+jobCode+"'";
		jdbcTemplate.update(deleteUploadLogSql);

		String updateUploadSql = "UPDATE t_upload_file l SET l.faile_number=l.faile_number-1,l.succes_number=l.succes_number+1 WHERE l.file_id='"+fileId+"'";
		jdbcTemplate.update(updateUploadSql);

		String updateDictSql = "UPDATE "+tableName+" l SET l.in_state=1 WHERE l.file_id='"+fileId+"' AND l.job_code='"+jobCode+"'";
		jdbcTemplate.update(updateDictSql);
	}


}
